/**
 * create by young (young2f@outlook.com) on 2021/10/12
 */
'use strict';

const XLSX = require('xlsx');
const moment = require('moment');

//引入xlsx
/**
 * 导入excel的函数
 * @param {*} file
 */
async function importsExcel(filePath) {
  // 读取Excel文件
  const workbook = XLSX.readFile(filePath);

  const sheetNames = workbook.SheetNames;
  console.log('sheet name', sheetNames);
  // 假设我们只处理第一个sheet
  const sheet = workbook.Sheets[sheetNames[0]];
  // 将sheet转换为JSON
  //console.log('sheet data', sheet);
  const data =await XLSX.utils.sheet_to_json(sheet);
  return data;
};

/**
 * 导出excel
 * @param {*} headers
 * @param {*} data
 * @param {*} fileName
 */
async function exportExcel(headers, data, fileName = 'treec') {
  const _headers = headers
    .map((item, i) => Object.assign({}, {
      key: item.key,
      title: item.title,
      position: String.fromCharCode(65 + i) + 1
    }))
    .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});
  const _data = data
    .map((item, i) => headers.map((key, j) => Object.assign({}, {
      content: item[key.key],
      position: String.fromCharCode(65 + j) + (i + 2)
    })))
    // 对刚才的结果进行降维处理（二维数组变成一维数组）
    .reduce((prev, next) => prev.concat(next))
    // 转换成 worksheet 需要的结构
    .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.content } }), {});

// 合并 headers 和 data
  const output = Object.assign({}, _headers, _data);
// 获取所有单元格的位置
  const outputPos = Object.keys(output);
// 计算出范围 ,["A1",..., "H2"]
  const ref = `${outputPos[0]}:${outputPos[outputPos.length - 1]}`;

// 构建 workbook 对象
//   const wb = {
//     SheetNames: ['mySheet'],
//     Sheets: {
//       mySheet: Object.assign(
//         {},
//         output,
//         {
//           '!ref': ref,
//           '!cols': [{wpx: 45}, {wpx: 100}, {wpx: 200}, {wpx: 80}, {wpx: 150}, {wpx: 100}, {wpx: 300}, {wpx: 300}],
//         },
//       ),
//     },
//   };
  const sheet = { SheetNames: [ fileName ] };
  const Sheets = {};
  Sheets[fileName] = Object.assign(
    {},
    output,
    {
      '!ref': ref,
      '!cols': [ { wpx: 45 }, { wpx: 100 }, { wpx: 200 }, { wpx: 80 }, { wpx: 150 }, { wpx: 100 }, { wpx: 300 }, { wpx: 300 } ],
    },
  ),
    sheet.Sheets = Sheets;
// 导出 Excel
  const day = moment()
    .format('-YYYY-MM-DD');
  XLSX.writeFile(sheet, `${fileName}${day}.xlsx`);
};

module.exports = {
  importsExcel,
  exportExcel
};
